#include <stdio.h> 
#include <stdlib.h> 
#include <unistd.h> 
#include <sqlite3.h>

/**
* @brief 插入记录 
* @param db 
*/

void do_insert(sqlite3 *db) {
    char name[64]="";
    char grades[64]="";
    const char ch[128]="";
    printf("请输入姓名:\n");
    scanf("%s",name);
    printf("请输入成绩:\n");
    scanf("%s",grades);
    sprintf((char *)ch,"insert into tbl1 values('%s',%s)",name,grades);
    printf("%s\n",ch);
    char *zErrMsg = NULL;
    
    if (sqlite3_exec(db, ch, NULL,NULL, &zErrMsg) != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    return;
}

/**
 * * @brief 删除记录 
 * * @param db 
 * */ 
void do_delete(sqlite3 *db) {
    char name[64]="";
    const char ch[128]="";
    printf("请输入姓名:\n");
    scanf("%s",name);
    sprintf((char *)ch,"delete from tbl1 where name='%s'",name);
    printf("%s\n",ch);
    char *zErrMsg = NULL;
    
    if (sqlite3_exec(db, ch, NULL,NULL, &zErrMsg) != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    return;
}

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i;
    for (i = 0; i < argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}
/*** @brief 利用回调显示 * @param db */ 

void do_show(sqlite3 *db) {
    char *zErrMsg = NULL;
    if (sqlite3_exec(db, "select * from tbl1", callback,NULL, &zErrMsg) != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    return;

}

/*** @brief 不使用回调显示 * @param db */ 
void do_show_sample(sqlite3 *db) {
    char *zErrMsg = NULL;
    int prow=0,pcloum=0;
    char **result=NULL;
    if (sqlite3_get_table(db,"select * from teacher",&result,&prow,&pcloum,&zErrMsg) != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }else{
        int num=pcloum;
        if(prow > 0 && pcloum > 0){
            for(int i=0;i<prow;i++){
                for(int j=0;j<pcloum;j++){
                    printf("%s = %s\n",result[j],result[num]);
                    num++;
                }
                printf("\n");
            }
        }
    }
    sqlite3_free_table(result);
    
    return;
}

int main() { 
    sqlite3 *db; 
    int n=-1; 
    char clean[64]=""; 
    char *zErrMsg = NULL;
    if (sqlite3_open("my.db", &db) < 0) { 
        printf("fail to sqlite3_open : %s\n", sqlite3_errmsg(db)); 
        return -1; 
    }
    if (sqlite3_exec(db, "create table tbl1(name text,grades int)", callback,NULL, &zErrMsg) != SQLITE_OK)
    {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
        while ( 1 ) {
            printf("**************************************************************\n"); 
            printf("1: insert record 2: delete record 3: show record 4: quit\n"); 
            printf("**************************************************************\n"); 
            printf("please select : "); 
            if (scanf("%d", &n) != 1) { 
                fgets(clean, 64, stdin); printf("\n"); 
                continue; }
                switch ( n ) {
                    case 1 : do_insert(db); 
                        break; 
                    case 2 : 
                        do_delete(db); 
                        break; 
                    case 3 : 
                        do_show_sample(db);
                        break; 
                    case 4 : 
                        sqlite3_close(db); 
                        exit(0); 
                    default: 
                    printf("err option\n"); 
                    break; 
                } 
        }
        return 0; 
}